package com.example.JavaWebDemo8.DAO;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.Properties;
import com.mysql.cj.*;

public class JDBCUtils {
    /**
     * 获取数据库连接，使用配置文件jdbc.properties获取数据库连接
     * 但是好像没成功
     * @return 数据库连接对象
     * @throws Exception
     */
    public static Connection getConnectionByConfigFile() throws Exception {
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties pros = new Properties();
        pros.load(is);
        String url = pros.getProperty("url");
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String classDriver = pros.getProperty("classDriver");
        Class<?> clazz = Class.forName(classDriver);

        Driver driver = (Driver) clazz.getDeclaredConstructor().newInstance();
        DriverManager.registerDriver(driver);
        return DriverManager.getConnection(url, user, password);
    }

    /**
     * 以硬编码的方式获取数据库连接
     * 用户名密码以及要连接的数据库在代码中，
     * @return 数据库连接对象
     * @throws Exception
     */
    public static Connection getConnectionByHardCode() throws Exception {
        String url = "jdbc:mysql://localhost:3306/student_management";
        String user = "root";
        String password = "password";
        String classDriver = "com.mysql.cj.jdbc.Driver";
        Class<?> clazz = Class.forName(classDriver);

        Driver driver = (Driver) clazz.getDeclaredConstructor().newInstance();
        DriverManager.registerDriver(driver);
        return DriverManager.getConnection(url, user, password);
    }

    /**
     * 关闭资源
     *
     * @param ps
     * @param conn
     */
    public static void closeResources(PreparedStatement ps, Connection conn) {
        try {
            if (ps != null) {
                ps.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * 关闭资源
     *
     * @param ps
     * @param conn
     * @param resultSet
     */
    public static void closeResources(PreparedStatement ps, Connection conn, ResultSet resultSet) {
        try {
            if (ps != null) {
                ps.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (resultSet != null)
                resultSet.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * 通用更新操作
     *
     * @param sql
     * @param args
     * @throws Exception
     */
    public static void update(String sql, Object... args) throws Exception {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnectionByHardCode();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeResources(ps, conn);
        }
    }

    /**
     * 针对Student对象的单个结果查询操作
     *
     * @param sql
     * @param args
     * @return
     * @throws Exception
     */
    public static Student query(String sql, Object... args) throws Exception {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Student student = null;
        try {
            conn = getConnectionByHardCode();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int cols = metaData.getColumnCount();
            if (rs.next()) {
                student = new Student();
                for (int i = 0; i < cols; i++) {
                    Field field = Student.class.getDeclaredField(metaData.getColumnName(i + 1));
                    field.setAccessible(true);
                    field.set(student, rs.getObject(i + 1));
                }
            }
        } catch (Exception e) {
            System.out.println("查询出现异常！！！" + e.toString());
            e.printStackTrace();
        } finally {
            closeResources(ps, conn, rs);
        }
        return student;
    }

    public <T> T querySingleObject(Class<T> clazz, String sql, Object... args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnectionByHardCode();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            if (rs.next()) {
                T t = clazz.getDeclaredConstructor().newInstance();
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = rs.getObject(i + 1);
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeResources(ps, conn, rs);
        }
        return null;
    }
}
